package cn.hpclub.server.service;

import cn.hpclub.server.constant.Constant;
import cn.hpclub.server.util.InterfaceUtil;
import cn.hpclub.server.util.excel.ExportUtils;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

/**
 * Created by wanglin on 2017/4/5.
 */
public class CmccOrderVmemberService {
    private static final Logger logger         = LoggerFactory.getLogger(BroadbandService.class);
    public static CmccOrderVmemberService service        = new CmccOrderVmemberService();

    public final static String   SELECT         = "SELECT vnet_records.*,org.NumCode as channel_code,org.DisplayName as channel_name,org_class.class_name as channel_type,date_format(vnet_records.time,'%Y/%m/%d %H:%i:%s') as opTime,(case vnet_records.v_type when 0 then '校园V网' when 1 then '合家欢' when 2 then '乡音网' end) as vType,(case vnet_records.op_type when 0 then '添加成员' when 1 then '删除成员' end) as opType,IF(orgType.orgIDTmp||orgType2.orgIDTmp2, '是', '否') AS is_accounts_manager ";

    public static final String[] HEADES_EXPORT  = { "V网名称", "V网编码", "业务类型", "V网成员", "成员短号", "资费名称", "费率",
            "店员姓名", "店员手机", "地市", "区县", "渠道编码", "渠道名称", "渠道类型", "客户经理", "办理时间" };
    public static final String[] COLUMNS_EXPORT = { "", "v_name", "v_code", "opType", "v_member", "v_short",
            "v_pkg_name", "v_pkg_rate", "user_name", "user_mobile", "city", "quxian", "channel_code", "channel_name",
            "channel_type", "is_accounts_manager", "opTime" };

    public static String getSelectSql(){
        String sql = "SELECT v_name,v_code,operate_type as opType,v_member,short_no as v_short,pkg_name as v_pkg_name,pkg_type as v_pkg_rate,user_name,user_mobile,channel_name,channel_code,display as quxian,city,";
        sql += "channel_type,account_manager as is_accounts_manager,DATE_FORMAT(create_time,'%Y/%m/%d %H:%i:%s') as opTime";
        return sql;
    }

    public String getSqlExceptSelect(ConcurrentHashMap<String, String> searchInfo){
        int topId = InterfaceUtil.getIdTop();
        String sqlExceptSelect = " FROM cmcc_order_vmember where 1=1";

        for(Map.Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQuery(item);
        }

        sqlExceptSelect += " order by cmcc_order_vmember.create_time desc";

        logger.debug("sqlExceptSelect = " + sqlExceptSelect);
        return sqlExceptSelect;
    }

    /**
     * @describe 获取基础拜访列表的excel文件
     * @param searchInfo
     *            查询条件参数列表
     * @return SXSSFWorkbook
     */
    public SXSSFWorkbook getExcel(ConcurrentHashMap<String, String> searchInfo){
        SXSSFWorkbook workbook = null;
        String sql = getSelectSql() + getSqlExceptSelect(searchInfo);
        List<Record> seq = Db.find(sql);

        String[] excelHeaders  = { "V网名称", "V网编码", "业务类型", "V网成员", "成员短号", "资费名称", "费率",
                "店员姓名", "店员手机", "地市", "区县", "渠道编码", "渠道名称", "渠道类型", "客户经理", "办理时间" };
        String[] keys = { "v_name", "v_code", "opType", "v_member", "v_short",
                "v_pkg_name", "v_pkg_rate", "user_name", "user_mobile", "city", "quxian", "channel_code", "channel_name",
                "channel_type", "is_accounts_manager", "opTime" };

        ExportUtils exportUtils = new ExportUtils(excelHeaders);
        try{
            exportUtils.exportExcel(keys, seq, "yyyy-MM-dd HH:ss:mm", 1);// 此处最后一个参数表示从第一行开始填入数据（表头为第0行）
            workbook = exportUtils.getSXSSFWorkbook();
        }
        catch(FileNotFoundException e){
            logger.error("CmccOrderGcgmService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an FileNotFoundException,error message:"
                    + e.getMessage());
        }
        catch(IOException ex){
            logger.error("CmccOrderGcgmService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an IOException,error message:"
                    + ex.getMessage());
        }
        return workbook;
    }



    private String buildQuery(Map.Entry<String, String> item){
        String sql = "";
        if(item.getKey().equals("username")){
            // 办理人姓名
            sql += " and cmcc_order_vmember.user_name like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("usermobile")){
            // 办理人手机
            sql += " and cmcc_order_vmember.user_mobile  like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("member")){
            // 成员手机
            sql += " and cmcc_order_vmember.v_member  like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("vgroupname")){
            // V网名称
            sql += " and cmcc_order_vmember.v_name  like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("vgroupcode")){
            // V网编码
            sql += " and cmcc_order_vmember.v_code  like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("startTime")){
            sql += " and cmcc_order_vmember.create_time >= '" + item.getValue() + " 00:00:00" + "' ";
        } else if(item.getKey().equals("endTime")){
            sql += " and cmcc_order_vmember.create_time <= '" + item.getValue() + " 23:59:59" + "' ";
        } else if(item.getKey().startsWith("isMgr")){
//            if("1".equals(item.getValue())){
//                sql += " and ((orgType.orgIDTmp is not null) or (orgType2.orgIDTmp2 is not null)) ";
//            } else if("0".equals(item.getValue())){
//                sql += " and ((orgType.orgIDTmp is  null) and (orgType2.orgIDTmp2 is null)) ";
//            }

        } else if(item.getKey().equals("org_id")){
            sql += " and cmcc_order_vmember.org_id in("
                    + InterfaceUtil.getChlidOrgIdSql(Integer.parseInt(item.getValue()), Constant.MAX_ORG_LEVEL, true)
                    + ") ";
        }
        return sql;
    }
}
